Configuring Relationships

Takeaways

• When calling child1.some_parent , SQLAlchemy determines when we load the parent from the database.

Why is it important to care about when we load parents?

• Joins are expensive.
• We should avoid having the user idling. Delays more than 150ms are noticeable, so milliseconds of performance matter!
• We should make sure the joins happen during a time and place in the UX that doesn't negatively impact the experience too much.

Lazy loading vs. Eager loading

Takeaways

Lazy loading

Load needed joined data only as needed. Default in SQLAlchemy.

• Pro: no initial wait time. Load only what you need.
• Con: produces a join SQL call every time there is a request for a joined asset.
  Bad if you do this a lot.

Eager loading

Load all needed joined data objects, all at once.

• Pro: reduces further queries to the database. Subsequent SQL calls read existing data
• Con: loading the joined table has a long upfront initial load time.

lazy=True (lazy loading) is the default option in db.relationship :

children = db.relationship('ChildModel', backref='some_parent', lazy=True)

Other loading options we can use

See the SQLAlchemy Docs on Relationship Loading Techniques for more loading options.

Other relationship options: collection_class and cascade

SQLAlchemy Docs on Relationship Options

• SQLALchemy ORM Relationship Docs

Takeaways

db.relationship

• Allows SQLAlchemy to identity relationships between models
• Links relationships with backrefs ( child1.some_parent )
• Configures relationship dynamics between parents and children, including options like lazy , collection_class , and cascade

Time for a Quiz!

QUIZ QUESTION: :

Match the loading technique to the definition or SQLAlchemy option

ANSWER CHOICES: